package com.tmxk.jdbc02.dao.impl;

import com.tmxk.jdbc02.dao.SalGradeDao;
import com.tmxk.jdbc02.entity.SalGrade;
import com.tmxk.jdbc02.utils.BaseDao;
import com.tmxk.jdbc02.vo.SalGradeVO;

import java.util.ArrayList;
import java.util.List;

public class SalGradeDaoImpl extends BaseDao implements SalGradeDao {
    @Override
    public SalGradeVO querySalGradeOne(String empNo) {
        //创建SalGrade实例对象
        SalGradeVO salGradeVO = null;
        // 定义查询sql语句
        StringBuffer sql = new StringBuffer();
       // sql.append("  SELECT S.GRADE, S.LOSAL,S.HISAL,E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM  ");
        sql.append("  SELECT S.GRADE, S.LOSAL,S.HISAL,E.EMPNO,E.ENAME,E.JOB,E.MGR,TO_CHAR(E.HIREDATE,'YYYY-MM-DD') HIREDATE,E.SAL,E.COMM  ");
        sql.append("  FROM EMP E , SALGRADE S   ");
        sql.append("  WHERE S.HISAL >= (SELECT SAL FROM EMP E WHERE E.EMPNO = ? )    ");
        sql.append("  AND  S.LOSAL <=(SELECT SAL FROM EMP E WHERE E.EMPNO = ? )  ");
        sql.append("  AND E.EMPNO = ?   ");
        // 循环遍历
        super.rs = super.executeQuery(sql.toString(),new Object[]{empNo,empNo,empNo});
        try {
            if (rs.next()) {
                //int grade, int losal, int hisal, int empNo, String eName, String job, int mgr,
                // String hireDate, double sal, double comm
                salGradeVO = new SalGradeVO(
                                rs.getInt("GRADE"),
                                rs.getInt("LOSAL"),
                                rs.getInt("HISAL"),
                                rs.getInt("EMPNO"),
                                rs.getString("ENAME"),
                                rs.getString("JOB"),
                                rs.getInt("MGR"),
                                rs.getString("HIREDATE"),
                                rs.getDouble("SAL"),
                                rs.getDouble("COMM")
                        );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            super.closeAllConn(rs,pstmt,conn);
        }

        return salGradeVO;
    }

    @Override
    public List<SalGrade> queryAllSalGrade() {
        List<SalGrade> salGrades = new ArrayList<>();
        SalGrade salGrade = null;
        //定义查询所有的sql语句
        String sql = " SELECT S.GRADE, S.LOSAL,S.HISAL FROM SALGRADE S ";
        super.rs = executeQuery(sql);
        try{
            // 循环遍历
            while (rs.next()){
                salGrade = new SalGrade(rs.getInt("GRADE"),
                        rs.getInt("LOSAL"),
                        rs.getInt("HISAL"));
                salGrades.add(salGrade);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            super.closeAllConn(rs,pstmt,conn);
        }
        return salGrades;
    }
}
